/*
 * Created by: 
 * Created: 28 November 2006
 */

using System;
using System.Data;
using System.Xml;
using BizUnit.Extensions.EntLib.Utilities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Services.BizTalkApplicationFramework.BizUnit;
using NUnit.Framework;

namespace BizUnit.Extensions.Tests
{
    [TestFixture]
    public class BizUnitUtilityHelperTests
    {
        #region " Helper tests "
        [Test]
        [Category("SQL")]
        public void GetDataSetWithSingleTable()
        {
            DataSet ds = EntLibDatabaseHelper.ExecuteSqlCommand(
                    @"..\..\Config\db.config", "AdventureWorks", "select * from [AdventureWorks].[Person].[Address];");
            Console.WriteLine(ds.DataSetName);
            Console.WriteLine(ds.Tables[0].TableName);
            Assert.AreEqual(1, ds.Tables.Count);
        }

        [Test]
        [Category("SQL")]
        public void GetDataSetWithMultipleTables()
        {
            DataSet ds = EntLibDatabaseHelper.ExecuteSqlCommand(
                    @"..\..\Config\db.config", "AdventureWorks", "select * from [AdventureWorks].[Person].[Address];select * from [AdventureWorks].[Person].[Contact];");
            Console.WriteLine(ds.DataSetName);
            Console.WriteLine(ds.Tables[0].TableName);
            Console.WriteLine(ds.Tables[1].TableName);
            Assert.AreEqual(2, ds.Tables.Count);
        }

        [Test]
        [Category("SQL")]
        [Ignore("Not migrated yet")]
        public void CheckConstraintsCorrectlyAddedToDataTablesInCreatedDataSet()
        {
            //TODO: Load from file to disconnect from database
            string schemaPath = @"..\..\TestData\DataSets\Schema\TestTable.xsd";
            string dataPath = @"..\..\TestData\DataSets\Data\TestTable.xml";
            string tableName = "TestTable";
            string keyName = "PK_Test";
            string primKeyColumns = "Col1";
            string dbGenedColumns = "Col1";
            
            DataSet ds = LoadDataSet(schemaPath, dataPath);

            XmlNode testConfig = BuildConfigNode(tableName, keyName, primKeyColumns, dbGenedColumns);
            
            EntLibDatabaseHelper.AddConstraints(ds, testConfig);
           
            Assert.AreEqual( 1, ds.Tables[tableName].Constraints.Count);
            Assert.AreEqual(ds.Tables[tableName].Constraints[0].ConstraintName, keyName);
            Assert.AreEqual(ds.Tables[tableName].Constraints[0].GetType().Name, "UniqueConstraint");

            Assert.IsTrue(ds.Tables[tableName].Columns["Col1"].AutoIncrement);
        }
        
        [Test]
        [Category("SQL")]
        [Ignore("Not migrated yet")]
        public void CheckConstraintsCorrectlyAddedToDataTablesInCreatedDataSetMultiplePKeys()
        {
            //TODO: Load from file to disconnect from database
            string schemaPath = @"..\..\TestData\DataSets\Schema\TestTable.xsd";
            string dataPath = @"..\..\TestData\DataSets\Data\TestTable.xml";
            
            //TODO: should be creating DAtaTables with table names
            DataSet ds = LoadDataSet(schemaPath, dataPath);

            string tableName = "TestTable";
            string keyName = "PK_Test";
            string primKeyColumns = "Col1|Col2";
            string dbGenedColumns = "";

            XmlNode testConfig = BuildConfigNode(tableName, keyName, primKeyColumns, dbGenedColumns);
            EntLibDatabaseHelper.AddConstraints(ds, testConfig);

            Assert.AreEqual(ds.Tables[tableName].Constraints.Count, 1);
            Assert.AreEqual(ds.Tables[tableName].Constraints[0].ConstraintName, keyName);
            Assert.AreEqual(ds.Tables[tableName].Constraints[0].GetType().Name, "UniqueConstraint");
        }

        [Test]
        [Category("SQL")]
        [Ignore("Not migrated yet")]
        public void CheckConstraintsCorrectlyAddedToDataTablesInCreatedDataSetMultipleGeneratedColumns()
        {
            //TODO: Load from file to disconnect from database
            string schemaPath = @"..\..\TestData\DataSets\Schema\TestTable.xsd";
            string dataPath = @"..\..\TestData\DataSets\Data\TestTable.xml";

            //TODO: should be creating DAtaTables with table names
            DataSet ds = LoadDataSet(schemaPath, dataPath);

            string tableName = "TestTable";
            string keyName = "";
            string primKeyColumns = "";
            string dbGenedColumns = "Col1|Col2";

            XmlNode testConfig = BuildConfigNode(tableName, keyName, primKeyColumns, dbGenedColumns);
            EntLibDatabaseHelper.AddConstraints(ds, testConfig);
            
            Assert.IsTrue(ds.Tables[tableName].Columns["Col1"].AutoIncrement);
            Assert.IsTrue(ds.Tables[tableName].Columns["Col2"].AutoIncrement);
        }

        [Test]
        [Category("Test harness tests")]
        public void LoadDataSetWithOneTableCheckFirstField()
        {
            string schemaPath = @"..\..\TestData\DataSets\Schema\TestTable.xsd";
            string dataPath = @"..\..\TestData\DataSets\Data\TestTable.xml";
            DataSet ds = LoadDataSet(schemaPath, dataPath);
            Assert.AreEqual(1, ds.Tables.Count);
        }
        
        private static DataSet LoadDataSet(string schemaPath, string dataPath)
        {
            DataSet ds = new DataSet();
            ds.ReadXmlSchema(schemaPath);
            ds.ReadXml(dataPath);
            return ds;
        }

        [Test]
        [Category("Test harness tests")]
       public void BuildConfigNode()
       {
           string tableName = "TestTable";
           string keyName = "PK_Test";
           string primKeyColumns = "Col1|Col2";
           string dbGenedColumns = "Col3";
           string testConfigNode = @"<TableConstraints>
                                        <Table name=""TestTable""> 
                                            <PrimaryKeyColumns keyName=""PK_Test"">Col1|Col2</PrimaryKeyColumns>
                                            <DBGeneratedColumns>Col3</DBGeneratedColumns>
                                        </Table>
                                       </TableConstraints>";
           XmlDocument expectedConfig = new XmlDocument();
           expectedConfig.LoadXml(testConfigNode);
           
           
           XmlNode testConfig = BuildConfigNode(tableName, keyName, primKeyColumns, dbGenedColumns);
          
           Assert.AreEqual(expectedConfig.OuterXml,testConfig.OuterXml);
       }
        
        
        private static XmlNode BuildConfigNode(string tableName, string keyName, string primKeyColumns, string dbGenedColumns)
        {
            string tableConstraintNode = @"<TableConstraints>
                                            <Table name=""{0}""> 
                                                <PrimaryKeyColumns keyName=""{1}"">{2}</PrimaryKeyColumns>
                                                <DBGeneratedColumns>{3}</DBGeneratedColumns>
                                            </Table>
                                           </TableConstraints>";
            
            tableConstraintNode = String.Format(tableConstraintNode, tableName,keyName,primKeyColumns, dbGenedColumns);
            XmlDocument testConfig = new XmlDocument();
            testConfig.LoadXml(tableConstraintNode);
            return testConfig.SelectSingleNode("/");
        }


        [Test]
        [Category("SQL")]
        public void GetDataSetFromSQLWithMultipleTableUsingHelper()
        {
            DataSet ds =
                EntLibDatabaseHelper.GetDataSetForExport(@"[AdventureWorks].[Person].[Address],[AdventureWorks].[Person].[Contact]",
                    @"..\..\Config\db.config", "AdventureWorks");
            Console.WriteLine(ds.DataSetName);
            Console.WriteLine(ds.Tables[0].TableName);
            Console.WriteLine(ds.Tables[1].TableName);
            Assert.AreEqual("[AdventureWorks].[Person].[Address]", ds.Tables[0].TableName);
            Assert.AreEqual("[AdventureWorks].[Person].[Contact]", ds.Tables[1].TableName);
            Assert.AreEqual(2, ds.Tables.Count);
        }

        //[Test]
        //[Category("SQL")]
        //[Ignore("Not migrated yet")]
        //public void EntLibSaveDataSetToSQLSingleRecord()
        //{
        //    string dataFile = @"..\..\TestData\Datasets\Data\EntLibAdventureWorks_InsertEmployees.xml";
        //    string schemaFile = @"..\..\TestData\Datasets\Schema\EntLibAdventureWorks_InsertEmployees.xsd";
        //    EntLibDatabaseHelper.ImportDatasetDataIntoDBTable(@"..\..\Config\db.config", "AdventureWorks", EntLibDatabaseHelper.GetDataSet(schemaFile, dataFile, 0));
        //    Database db = EntLibDatabaseHelper.GetDatabase(@"..\..\Config\db.config", "AdventureWorks");
        //    DataSet ds = db.ExecuteDataSet(CommandType.Text, String.Format("select * from employees where LastName = '{0}'", "Mac Criosta"));
        //    Assert.AreEqual(1, ds.Tables[0].Rows.Count);
        //    //TODO: Use DataAssert to test dataSets/dataTables etc.
        //    //cleanup
        //    db.ExecuteNonQuery(CommandType.Text, "delete from employees where LastName = 'Mac Criosta'");
        //}

        //[Test]
        //[Category("SQL")]
        //public void SaveDataSetToSQL()
        //{
        //    string dataFile = @"..\..\TestData\Datasets\Data\EntLibAdventureWorks_InsertEmployees.xml";
        //    string schemaFile = @"..\..\TestData\Datasets\Schema\EntLibAdventureWorks_InsertEmployees.xsd";
        //    string cnString = @"Persist Security Info=False;Integrated Security=SSPI;database=AdventureWorks;server=(local);Connect Timeout=30";
        //    ImportDatasetToDBStep idb = new ImportDatasetToDBStep();
        //    idb.ImportDatasetDataIntoDBTable(cnString, idb.GetDataSet(schemaFile, dataFile, 0));

        //    Database db = EntLibDatabaseHelper.GetDatabase(@"..\..\Config\db.config", "AdventureWorks");
        //    DataSet ds = db.ExecuteDataSet(CommandType.Text, String.Format("select * from employees where LastName = '{0}'", "Mac Criosta"));
        //    Assert.AreEqual(1, ds.Tables[0].Rows.Count);

        //    //cleanup
        //    db.ExecuteNonQuery(CommandType.Text, "delete from employees where LastName = 'Mac Criosta'");

        //}
        #endregion

    }
}